package cn.com.dhcc.ddi.datasource.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.dao.SimpleDaoForOracle;
import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.app.core.exception.NiceException;
import cn.com.dhcc.app.core.exception.ServiceException;
import cn.com.dhcc.app.core.handler.modulehandler.WhereCondition;
import cn.com.dhcc.ddi.datasource.vo.DataSourceVo;
import cn.com.dhcc.ddi.datasource.vo.TableColVo;
import cn.com.dhcc.ddi.datasource.vo.TableInfoVo;
import cn.com.dhcc.ddi.datasource.vo.wapper.TableColRowWapper;
import cn.com.dhcc.ddi.datasource.vo.wapper.TableInfoRowWapper;
/**
 * 
 * DataSourceSQLserverExtDao
 * @Author: fxx
 * @date: 2017-5-16
 *
 */
@Repository
public class DataSourceSQLserverExtDao extends BaseDao {
	private SimpleDaoForOracle dao;
	@Autowired
	public DataSourceDao datasourceDao;
	/**
	 * 现根据界面上的连接信息初始化数据源
	 * 
	 * @param vo
	 * @throws NiceException
	 * @throws ServiceException 
	 * @throws DaoException 
	 */
	public void initDao(DataSourceVo vo) throws DaoException {
		try {
			dao = datasourceDao.initDao(vo);
		} catch (DaoException e) {
			throw new DaoException("初始化数据源错误", e);
		}
	}
	/**
	 * 初始化数据源
	 * @param dataSource
	 * @return
	 * @throws NiceException
	 */
	public SimpleDaoForOracle getDaoByPublishDataSource(DataSourceVo dataSource)  throws  DaoException{
		final SimpleDaoForOracle dao = new SimpleDaoForOracle();
		try {
			dao.initDataSource(dataSource.getDataSourceConfVo().getDriverName(), dataSource.getDataSourceConfVo().getUrl(), dataSource.getDataSourceConfVo().getUserName(), dataSource.getDataSourceConfVo().getPassword());
		} catch (Exception e) {
			throw new DaoException("初始化数据源失败", e);
		}

		return dao;
	}
    
	/**
     * 获取表结构  sqlserver(2000)
     * @param schema
     * @param tableName
     * @return
     * @throws DaoException
     */

    public List<TableColVo> getUserTabColumnsForSqlserverext(String schema,String tableName) throws DaoException {
        String msg = "";
        String sql = "SELECT " +
                "    col_name = convert(varchar(100),a.name)," +
                "    is_pk = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (" +
                "                     SELECT name FROM sysindexes WHERE indid in(" +
                "                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '1' else '' end," +
                "    col_type = convert(varchar(100),b.name)," +
                "    col_default = convert(varchar(100),e.text) ," +
                "    col_comment = convert(varchar(100),g.value) " +
                " FROM syscolumns a " +
                " left join  systypes b " +
                " on  a.xusertype=b.xusertype " +
                " inner join  sysobjects d " +
                " on  a.id=d.id  and d.xtype in ('U','V') and  d.name<>'dtproperties' " +
                " left join  syscomments e " +
                " on a.cdefault=e.id" +
                " left join  sysproperties g " +
                " on  a.id=g.id and a.colid=g.smallid  " +
                " left join  sysproperties f " +
                " on  d.id=f.id and f.smallid=0" +
                " where  d.name=?" +
                " order by a.id,a.colorder";
        try {
            return dao.list("获取表元数据", sql, new TableColRowWapper(),
                    new Object[] { tableName });
        } catch (DaoException e) {
            msg = "获取表元数据失败";
            throw new DaoException(msg, e);
        }
    }

    /**
     * 获取所有被授权的表
     * @return
     * @throws DaoException
     */
    public List<TableInfoVo> getUserTablesForSqlserverext()throws DaoException {
        String msg = "";
        String sql =" select t1.table_name,t1.schema_name,convert(varchar(256),t2.table_comment) from " +
                    "    (select table_name,table_schema schema_name from information_schema.TABLES) t1 " + 
                    " left join " + 
                    "    (select tbs.name table_name,tbs.value table_comment from sysobjects ds,sysproperties tbs where ds.id = tbs.id) t2 " + 
                    " on t1.table_name = t2.table_name " +
                    " where  t1.table_name not in('dtproperties','sysconstraints','syssegments')" +
                    " order by t1.table_name ";
        try {
            return dao.list("获取所有被授权的表", sql, new TableInfoRowWapper(),
                    new Object[] { });
        } catch (DaoException e) {
            msg = "获取所有表失败";
            throw new DaoException(msg, e);
        }
    }
    
    /**
     * 获取所有被授权的表
     * @return
     * @throws DaoException
     */
    public List<String> getUserTablesNameForSqlserverext()throws DaoException {
        String msg = "";
        String sql = "select table_name from information_schema.TABLES where  table_name not in('dtproperties','sysconstraints','syssegments')";
        try {
            WhereCondition where = new WhereCondition();
            return dao.listSignleStringColumn("获取所有被授权的表", sql, where,
                    new Object[] { });
        } catch (DaoException e) {
            msg = "获取所有表失败";
            throw new DaoException(msg, e);
        }
    }
    
}
